import connection from '@/app/database'
import { addRoleParams, IGetListParams, IUser } from '@/types/user.types'

class UserService {
  /**
   * 新增用户信息
   * @param user
   * @returns
   */
  public async create(user: IUser) {
    const { username, password } = user
    const statement: string = `INSERT INTO users (username, password) VALUES(?, ?);`
    return connection.execute(statement, [username, password])
  }

  /**
   * 通过用户名查找用户
   * @param username
   * @returns
   */
  public getUserByName(username: string) {
    const statement = `SELECT * FROM users WHERE username = ?;`
    return connection.execute(statement, [username])
  }

  /**
   * 用户关联角色
   * @param params
   * @returns
   */
  public addRole(params: addRoleParams) {
    const {roleID, userID} = params
    const statement = `INSERT INTO user_role (userID, roleID) VALUES(?, ?)`
    return connection.execute(statement, [userID, roleID])
  }

  /**
   * 获取用户对应的菜单权限
   * @param id
   * @returns
   */
  public getMenu(id: number) {
    const statement = `
        SELECT DISTINCT
            P.id permissionID,
            P.name menuName,
            P.pid pid,
            P.type type,
            P.icon icon,
            P.component component,
            P.path path,
            P.level level,
            P.permissionValue permissionValue
        FROM
            users U
        JOIN
            user_role UR
        ON
            U.id = UR.userID AND U.id = ?
        JOIN
            roles R
        ON
            UR.roleID = R.id
        JOIN
            role_ferm RF
        ON
            R.id = RF.roleID
        JOIN
            permissions P
        ON
            RF.permID = P.id;
        `;
    return connection.execute(statement, [id])
  }

  /**
   * 获取用户列表
   */
  public getList(params: IGetListParams) {
    const { pageNum = '1', pageSize = '5', username, phone }  = params
    const offset = Number(pageSize) * (Number(pageNum) - 1)
    let statement = `SELECT * FROM users `
    if (username || phone) {
      if (username && phone) {
        statement += `WHERE phone = ${phone} AND username LIKE '%${username}%' ORDER BY updateTime DESC LIMIT ?, ?;`
      } else if (username) {
        statement += `WHERE username LIKE '%${username}%' ORDER BY updateTime DESC LIMIT ?, ?;`
      } else if (phone) {
        statement += `WHERE phone = ${phone} ORDER BY updateTime DESC LIMIT ?, ?;`
      }
    } else {
      statement += `ORDER BY updateTime DESC LIMIT ?, ?;`
    }
    return connection.execute(statement, [String(offset), pageSize])
  }

  /**
   * 获取用户列表count
   */
  public getListCount(params: IGetListParams) {
    const { username, phone }  = params
    let statement = `SELECT COUNT(id) as count from users `
    if (username || phone) {
      if (username && phone) {
        statement += `WHERE phone = ${phone} AND username LIKE '%${username}%';`
      } else if (username) {
        statement += `WHERE username LIKE '%${username}%';`
      } else if (phone) {
        statement += `WHERE phone = ${phone};`
      }
    }
    console.log(statement)

    return connection.query(statement)
  }
}

export default new UserService()